﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace ICE.Vacations.DataBase
{
    public class OracleDataClass
    {
        private OracleConnection oracleConnection { get; set; }

        public void OpenConnection()
        {
            try
            {
                oracleConnection = new OracleConnection();
                oracleConnection.ConnectionString = "User Id =PRUEBA; Password=prueba123; Data Source=XE;";
                oracleConnection.Open();
            }
            catch (OracleException oracleExc)
            {
                throw (oracleExc);
            }
        }

        public void CloseConnection()
        {
            try
            {
                oracleConnection.Close();
                oracleConnection.Dispose();
            }
            catch (OracleException oracleExc)
            {
                throw (oracleExc);
            }
        }


        public int TOTAL_DIAS(string employeeID)
        {
            try
            {
                this.OpenConnection();
                OracleCommand oracleCommand = new OracleCommand();
                oracleCommand.Connection = oracleConnection;

                oracleCommand.CommandType = CommandType.StoredProcedure;
                oracleCommand.CommandText = "TOTAL_DIAS"; //Nombre el StoreProcedure

                OracleParameter parameter = oracleCommand.Parameters.Add("EMPLOYEE_ID", OracleType.VarChar);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = employeeID;


                oracleCommand.Parameters.Add("TOTAL", OracleType.Number).Direction = ParameterDirection.Output;
                oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);
                return Convert.ToInt32(oracleCommand.Parameters["TOTAL"].Value);

            }
            catch (OracleException oraclExcep)
            {
                this.CloseConnection();
                throw (oraclExcep);
            }
        }
        public OracleDataReader OracleRead(string query, string procedureName)
        {
            try
            {
                this.OpenConnection();
                OracleCommand oracleCommand = new OracleCommand();
                oracleCommand.Connection = oracleConnection;

                oracleCommand.CommandType = CommandType.StoredProcedure;
                oracleCommand.CommandText = procedureName; //Nombre el StoreProcedure

                OracleParameter parameter = oracleCommand.Parameters.Add("EMPLOYEE_ID", OracleType.VarChar);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = query;


                oracleCommand.Parameters.Add("TOTAL", OracleType.Number).Direction = ParameterDirection.Output;
                return oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (OracleException oraclExcep)
            {
                this.CloseConnection();
                throw (oraclExcep);
            }
        }


    }
}
